package exercise;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class EX02_AccountDAO implements EX02_IAccountDAO {
	
	private final String DRV = "oracle.jdbc.driver.OracleDriver";
	private final String URL = "jdbc:oracle:thin:jwj8508/jwj8508@112.216.253.125:1521:xe";
	private String insertSQL = "insert into ex02account values (?, ?, ?, sysdate)";
	private String updateSQL = "update ex02account set balance = ?, interestrate = ? where aid = ?";
	private String deleteSQL = "delete ex02account where aid = ?";
	private String selectSQL = "select * from ex02account order by aid desc";
	
	
	
	private Connection openDatabase() {
		try {
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		} catch (Exception ex) {
			ex.printStackTrace();
		}		
		return null;
	}
	private void closeDatabase(Connection c, PreparedStatement p, ResultSet r) {
		if(r != null) try {r.close(); r = null;} catch (Exception e){}
		if(p != null) try {p.close(); p = null;} catch (Exception e){}
		if(c != null) try {c.close(); c = null;} catch (Exception e){}
	}

	@Override
	public boolean insertAccount(EX02_AccountVO a) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setString(1, a.getAid());
			pstmt.setInt(2, a.getBalance());
			pstmt.setFloat(3, a.getInterestRate());			
			
			pstmt.executeUpdate();		
			
			result = true;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally{
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}

	@Override
	public boolean updateAccount(EX02_AccountVO a) {
		Connection conn  = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(updateSQL);			
			pstmt.setInt(1, a.getBalance());
			pstmt.setFloat(2, a.getInterestRate());			
			pstmt.setString(3, a.getAid());
			
			int cnt = pstmt.executeUpdate();		
			
			if (cnt == 0) System.out.println("수정 불가능");
			
			result = true;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally{
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}

	@Override
	public boolean deleteAccount(EX02_AccountVO a) {
		Connection conn  = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(deleteSQL);			
			pstmt.setString(1, a.getAid());
			
			int cnt = pstmt.executeUpdate();		
			
			if (cnt == 0) System.out.println("삭제 불가능");
			
			result = true;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally{
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}

	@Override
	public List<EX02_AccountVO> selectAccount() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		List<EX02_AccountVO> result = new ArrayList<>();
		
		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				EX02_AccountVO a = new EX02_AccountVO();
				a.setAid(rs.getString("aid"));
				a.setBalance(rs.getInt("balance"));
				a.setInterestRate(rs.getFloat("interestrate"));		
				a.setDateCreated(rs.getString("datecreated"));
				
				result.add(a);
			}
			
			
		} catch (Exception ex){
			ex.printStackTrace();
		} finally {
			this.closeDatabase(conn, pstmt, rs);
		}		
		return result;
	}

}
